package dao.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.Date;
import model.PublicHolidayDTO;
import dao.DAOException;
import dao.PublicHolidayDAO;
import dao.mysql.util.*;

public class PublicHolidayDAOImpl implements PublicHolidayDAO {

	private Connection conn;
	@Override
	public void insertPublicHoliday(PublicHolidayDTO pubDay)
			throws DAOException {
		// TODO Auto-generated method stub
		String insertsql = "Insert Into holiday Values (?,?,?) ";
		try
		{
			conn = MySQLUtility.getConnection();
		}
		catch(ClassNotFoundException e1)
		{
			e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try
		{
			conn.setAutoCommit(false);
            
            PreparedStatement ps = conn.prepareStatement(insertsql);
            ps.setDate(1,(java.sql.Date) pubDay.getStartdate());
            ps.setDate(2, (java.sql.Date) pubDay.getEnddate());
            ps.setString(3,  pubDay.getDescription());
           

            ps.executeUpdate();

            
            conn.commit();
            ps.close();
		}
		catch(Exception e)
		{
		
			String error = "Error selecting users. Nested Exception is:" + e;
			//Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
			
		}
		finally
		{
			try
			{
				conn.rollback();
				conn.close();
			}
			catch(Exception e)
			{
			
			}
		}
	}

	@Override
	public void updatePublicHoliday(PublicHolidayDTO pubDay) throws DAOException{
		// TODO Auto-generated method stub
		String updateSql = "UPDATE holiday SET Description =?  WHERE  StartDate= ? AND EndDate= ?";
		try
		{
			conn = MySQLUtility.getConnection();
		}
		catch(ClassNotFoundException e1)
		{
			e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try
		{
			
			conn.setAutoCommit(false);

			 PreparedStatement ps = conn.prepareStatement(updateSql);
			 ps.setString(1, pubDay.getDescription());
	          ps.setDate(2,(java.sql.Date) pubDay.getStartdate());
	          ps.setDate(3, (java.sql.Date) pubDay.getEnddate());
	         
	 
	           
	          ps.executeUpdate();
	          conn.commit();ps.close();
			//Logger.getLogger(getClass().getName()).log(Level.INFO, "Excecuting Update:" + updateSql);
			ps.close();
		}
		catch(Exception e)
		{
		
			String error = "Error selecting users. Nested Exception is:" + e;
			//Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
			
		}
		finally
		{
			try
			{
				conn.rollback();
				conn.close();
			}
			catch(Exception e)
			{
			
			}
		}
	}

	@Override
	public void deletePublicHoliday(PublicHolidayDTO pubDay) throws DAOException{
		// TODO Auto-generated method stub
		String deleteSql = "DELETE FROM holiday WHERE StartDate= ? AND EndDate= ?";
		try
		{
			conn = MySQLUtility.getConnection();
		}
		catch(ClassNotFoundException e1)
		{
			e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try
		{
			
			conn.setAutoCommit(false);

			 PreparedStatement ps = conn.prepareStatement(deleteSql);
			 
	          ps.setDate(1,(java.sql.Date) pubDay.getStartdate());
	          ps.setDate(2, (java.sql.Date) pubDay.getEnddate());
	         
	 
	           
	          ps.executeUpdate();
	          conn.commit();ps.close();
			//Logger.getLogger(getClass().getName()).log(Level.INFO, "Excecuting Delete:" + deleteSql);
			ps.close();
		}
		catch(Exception e)
		{
		
			String error = "Error selecting users. Nested Exception is:" + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
			
		}
		finally
		{
			try
			{
				conn.rollback();
				conn.close();
			}
			catch(Exception e)
			{
			
			}
		}
	}

	@Override
	public PublicHolidayDTO findpubDay(Date startDate, Date endDate)
			throws DAOException {
		// TODO Auto-generated method stub
		try {
			conn = MySQLUtility.getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PublicHolidayDTO pubday = new PublicHolidayDTO();
		String selectSql = "SELECT * FROM holiday WHERE StartDate=? AND EndDate = ?";
		
		try {
			 
             
             PreparedStatement ps = conn.prepareStatement(selectSql);
             ps.setDate(1, (java.sql.Date) startDate );
             ps.setDate(2, (java.sql.Date) endDate);
             ResultSet rs=ps.executeQuery();
          
             if(rs.next())
             {
            	 pubday.setStartdate(rs.getDate("startdate"));
            	 pubday.setEnddate(rs.getDate("enddate"));
            	 pubday.setDescription(rs.getString("Description"));
            	
   
             }
            ps.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			//Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return pubday;
	
	}

	@Override
	public ArrayList<PublicHolidayDTO> findallpubDay() throws DAOException {
		// TODO Auto-generated method stub
		ArrayList<PublicHolidayDTO> items = new ArrayList<PublicHolidayDTO>();
		try {
			conn = MySQLUtility.getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String selectSql = "SELECT * FROM holiday";
		
		try {
			
    
             PreparedStatement ps = conn.prepareStatement(selectSql);
             ResultSet rs=ps.executeQuery();
             PublicHolidayDTO pubday ;
             while(rs.next())
             {
            	 pubday = new PublicHolidayDTO();
            	 pubday.setStartdate(rs.getDate("startdate"));
            	 pubday.setEnddate(rs.getDate("enddate"));
            	 pubday.setDescription(rs.getString("description"));
            	 items.add(pubday);
   
             }
            ps.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			//Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return items;
	
	}



}
